﻿package com.ypan.monitor.database;

import java.io.ByteArrayOutputStream;
import java.io.IOException;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.graphics.Bitmap;

import com.ypan.monitor.MainApp;
import com.ypan.monitor.utils.Define;

/**
 * @ClassName: DBHelper
 * @Description: DBHelper
 * @author Michael.Pan
 * @date 2012-6-18 下午02:13:00
 */

public class DBHelper {
	private static final String TAG = "DBHelper";
	// Define for DB
	private static final String DB_NAME = "uninstall.db"; // DB's name
	private static final String DB_TABLE = "APP_MGR"; // DB's TABLE name
	private static final int DB_VERSION = 1; // DB's Version

	private static final String PACKAGE_NAME = "pkg";
	private static final String APP_NAME = "app";
	private static final String ICON = "icon";
	private static final String VERSION = "version";
	private static final String VERSION_CODE = "versionCode";
	private static final String DATA_DIR = "dataDir";
	private static final String MODIFIED_TIME = "modifiedTime";
	private static final String APK_SIZE = "apkSize";

	// cmd string for create table
	private static final String DB_CREATE = "CREATE TABLE " + DB_TABLE + " ("
			+ PACKAGE_NAME + " TEXT," + APP_NAME + " TEXT," + ICON + " TEXT,"
			+ VERSION + " TEXT," + VERSION_CODE + " TEXT," + DATA_DIR
			+ " TEXT," + MODIFIED_TIME + " TEXT," + APK_SIZE + " TEXT)";

	private Context mContext = null;
	private SQLiteDatabase mSQLiteDatabase = null;
	private DatabaseHelper mDatabaseHelper = null;

	private static class DatabaseHelper extends SQLiteOpenHelper {
		DatabaseHelper(Context context) {
			super(context, DB_NAME, null, DB_VERSION);
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			db.execSQL(DB_CREATE);
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			// TODO
		}
	}

	public DBHelper() {
		mContext = MainApp.sInstance;
	}

	// open db
	public void open() throws SQLException {
		mDatabaseHelper = new DatabaseHelper(mContext);
		mSQLiteDatabase = mDatabaseHelper.getWritableDatabase();
	}

	// close db
	public void close() {

		if (mSQLiteDatabase != null) {
			mSQLiteDatabase.close();
			mSQLiteDatabase = null;
		}

		if (mDatabaseHelper != null) {
			mDatabaseHelper.close();
			mDatabaseHelper = null;
		}
	}

	// beginTransaction
	public void beginTransaction() {
		mSQLiteDatabase.beginTransaction();
	}

	// endTransaction
	public void endTransaction() {
		mSQLiteDatabase.setTransactionSuccessful();
		mSQLiteDatabase.endTransaction();
	}

	// insert a data
	public long insertData(String packageName, String appName, Bitmap icon,
			String version, int versionCode, String dataDir, long modifiedTime,
			long apkSize) {
		ContentValues initialValues = initContentValues(packageName, appName,
				icon, version, versionCode, dataDir, modifiedTime, apkSize);
		return mSQLiteDatabase.insert(DB_TABLE, null, initialValues);
	}

	// insert a data
	// public long insertData(ApkInfo apkInfo) {
	//
	// ContentValues initialValues = initContentValues(apkInfo.packageName,
	// apkInfo.appName,
	// apkInfo.icon, apkInfo.version, apkInfo.versionCode,
	// apkInfo.dataDir, apkInfo.modifiedTime, apkInfo.apkSize);
	//
	// return mSQLiteDatabase.insert(DB_TABLE, null, initialValues);
	// }

	// delete a data
	public boolean deleteData(String packageName) {
		String where = PACKAGE_NAME + " = ?";
		String[] whereValue = { packageName };
		return mSQLiteDatabase.delete(DB_TABLE, where, whereValue) > 0;
	}

	// fetch all data
	public Cursor fetchAllData(int sortType) {
		String[] columns = new String[] { PACKAGE_NAME, APP_NAME, ICON,
				VERSION, VERSION_CODE, DATA_DIR, MODIFIED_TIME, APK_SIZE };
		String sort = MODIFIED_TIME;
		if (sortType == Define.SORT_BY_TIME_DESC) {
			sort = MODIFIED_TIME + " DESC";
		} else if (sortType == Define.SORT_BY_TIME_ASC) {
			sort = MODIFIED_TIME + " ASC";
		} else if (sortType == Define.SORT_BY_NAME_DESC) {
			sort = APP_NAME + " DESC";
		} else if (sortType == Define.SORT_BY_NAME_ASC) {
			sort = APP_NAME + " ASC";
		}
		return mSQLiteDatabase.query(DB_TABLE, columns, null, null, null, null,
				sort);
	}

	// query a data by package's name
	public Cursor fetchData(String packageName) throws SQLException {
		String where = PACKAGE_NAME + " = ?";
		String[] whereValue = { packageName };
		String[] columns = new String[] { PACKAGE_NAME, APP_NAME, ICON,
				VERSION, VERSION_CODE, DATA_DIR, MODIFIED_TIME, APK_SIZE };
		Cursor mCursor = mSQLiteDatabase.query(true, DB_TABLE, columns, where,
				whereValue, null, null, null, null);
		return mCursor;

	}

	// update data by package's name
	public boolean updateData(String packageName, String appName, Bitmap icon,
			String version, int versionCode, String dataDir, long modifiedTime,
			long apkSize) {

		ContentValues initialValues = initContentValues(packageName, appName,
				icon, version, versionCode, dataDir, modifiedTime, apkSize);

		String where = PACKAGE_NAME + " = ?";
		String[] whereValue = { packageName };
		return mSQLiteDatabase.update(DB_TABLE, initialValues, where,
				whereValue) > 0;
	}

	private ContentValues initContentValues(String packageName, String appName,
			Bitmap icon, String version, int versionCode, String dataDir,
			long modifiedTime, long apkSize) {

		ContentValues initialValues = new ContentValues();
		initialValues.put(PACKAGE_NAME, packageName);
		initialValues.put(APP_NAME, appName);
		try {
			ByteArrayOutputStream outStream = new ByteArrayOutputStream();
			icon.compress(Bitmap.CompressFormat.PNG, 100, outStream);
			initialValues.put(ICON, outStream.toByteArray());
			outStream.flush();
			outStream.close();
		} catch (IOException e) {
			e.printStackTrace();
		}
		initialValues.put(VERSION, version);
		initialValues.put(VERSION_CODE, versionCode);
		initialValues.put(DATA_DIR, dataDir);
		initialValues.put(MODIFIED_TIME, modifiedTime);
		initialValues.put(APK_SIZE, apkSize);
		return initialValues;
	}
}
